package com.webapp.model;

import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

public class CircleModel {
	
	private Statement stmt = DatabaseConnector.connect();

	//creates a new cicle
	public boolean createCircle(Circle newCircle){
		try{
			if(!(stmt.executeQuery("SELECT * FROM circles WHERE Circle_Name = '" +newCircle.getCircleName()+ "'").next())){
				ResultSet rs = stmt.executeQuery("SELECT Circle_Id FROM circles");
				int id;
				
				while(true){
					id = new Double( Math.random() * 10000 ).intValue();
					if(!(stmt.executeQuery("SELECT Circle_Id FROM circles WHERE Circle_Id = '" +id+ "'")).next()){
						break;
					}
				}
				newCircle.setCircleId(id);
				
				stmt.executeUpdate("INSERT INTO circles (Circle_Id, Circle_NAME, Owner_Of_Circle, Type) VALUES ('" +
								newCircle.getCircleId()+ "', '" +newCircle.getCircleName()+ "', '" +newCircle.getCircleOwner()+
								"', '" +newCircle.getCircleType()+ "')");
				stmt.executeUpdate("INSERT INTO circlemembership (CustomerID, CircleID) VALUES ('"+
								newCircle.getCircleOwner()+ "', '" +newCircle.getCircleId()+ "')");
				return true;
			}
			else
				return false;
		}catch(Exception ex){return false;}
	}
	
	//deletes an existing circle
	public boolean deleteCircle(int circleId){
		try{
			if(stmt.executeQuery("SELECT * FROM circles WHERE Circle_Id = '" +circleId+ "'").next()){
				stmt.executeUpdate("DELETE FROM circles WHERE Circle_Id = '" +circleId+ "'");
				stmt.executeUpdate("DELETE FROM circlemembership WHERE CircleID = '" +circleId+ "'");
				ResultSet rs = stmt.executeQuery("SELECT Post_Id FROM posts WHERE Circle = '" +circleId+ "'");
				PostModel m = new PostModel();
				while(rs.next())
					m.deletePost(rs.getInt(1));
				
				return true;
			}
			else
				return false;
		}catch(Exception ex){return false;}
	}
	
	//renames an existing circle
	public boolean renameCircle(String name, int circleId){
		try{
			if(stmt.executeQuery("SELECT * FROM circles WHERE Circle_Id = '" +circleId+ "'").next()){
				stmt.executeUpdate("UPDATE circles SET Circle_NAME = '" +name+ "' WHERE Circle_Id = '" +circleId+ "'");
				return true;
			}
			else
				return false;
		}catch(Exception ex){return false;}
	}
	
	//deletes a user from an existing circle
	public boolean removeUser(int userId, int circleId){
		try{
			if(stmt.executeQuery("SELECT * FROM circlemembership WHERE CustomerID = '" +userId+ 
									"' AND CircleID = '" +circleId+ "'").next()){
				stmt.executeUpdate("DELETE FROM circlemembership WHERE CircleID = '" +circleId+ 
								"' AND CustomerID = '" +userId+ "'");
				return true;
			}
			else
				return false;
		}catch(Exception ex){return false;}
	}
	
	//adds a user to an existing circle
	public boolean addUser(int userId, int circleId){
		try{
			if(stmt.executeQuery("SELECT * FROM circles WHERE Circle_Id = '" +circleId+ "'").next()){
				stmt.executeUpdate("INSERT INTO circlemembership (CustomerID, CircleID) VALUES ('" +userId+
									"', '" +circleId+ "')");
				return true;
			}
			else
				return false;
		}catch(Exception ex){return false;}
	}
	
	/**
	 * Method that searches for a user
	 * @param firstName first name of the user
	 * @param lastName last name of the user
	 * @return the UserId of the desired user
	 */
	public int searchForUserByName(String firstName, String lastName){
		try{
			ResultSet rs = stmt.executeQuery("SELECT T.User_Id FROM users T, persons P WHERE P.Last_Name = '" +lastName+ "' AND"+
					" P.First_Name = '" +firstName+ "' AND T.SSN = P.SSN");
			if(rs.next())
				return rs.getInt(1);
			else
				return -1;
			
		}catch(Exception ex){return -1;}
	}
	
	public int searchForUserByEmail(String email){
		try{
			ResultSet rs = stmt.executeQuery("SELECT T.User_Id FROM persons P, users T WHERE P.Email_Address = '" +email+ "' AND"+
					"T.SSN = P.SSN");
			if(rs.next())
				return rs.getInt(1);
			else
				return -1;
		}catch(Exception ex){return -1;}
	}
	
	/*
	 * Returns a list of circle ID's a specific user OWNS (Not circles they are a part of)
	 */
	public ArrayList<Circle> getCircleIdOwnerList(int userId){
		try{
			ResultSet rs = stmt.executeQuery("SELECT * FROM circles WHERE Owner_Of_Circle = '" +userId+ "'");
			ArrayList<Circle> list = new ArrayList<Circle>();
			
			while(rs.next()){
				Circle circle = new Circle();
				circle.setCircleId(rs.getInt(1));
				circle.setCircleName(rs.getString(2));
				circle.setCircleOwner(rs.getInt(3));
				circle.setCircleType(rs.getString(4));
				
				list.add(circle);
			}
			
			return list;
		}catch(Exception ex){return null;}
	}
	
	/*
	 * Returns a Circle object based on the Circle ID provided
	 */
	public Circle getCircle(int circleId){
		try{
			ResultSet rs = stmt.executeQuery("SELECT * FROM circles WHERE Circle_Id = '" +circleId+ "'");
			if(rs.next()){
				Circle circle = new Circle();
				circle.setCircleId(rs.getInt(1));
				circle.setCircleName(rs.getString(2));
				circle.setCircleOwner(rs.getInt(3));
				circle.setCircleType(rs.getString(4));
				
				return circle;
			}
			else
				return null;
		}catch(Exception ex){return null;}
	}
	
	/*
	 * Returns a list of circle ID's a specific user is A PART OF (Including circles they own)
	 */
	public ArrayList<Circle> getCircleIdMembershipList(int userId){
		try{
			ResultSet rs = stmt.executeQuery("SELECT CircleID FROM circlemembership WHERE CustomerID = '" +userId+ "'");
			ArrayList<Circle> list = new ArrayList<Circle>();
			
			while(rs.next()){
				ResultSet rs2 = stmt.executeQuery("SELECT * FROM circles WHERE Circle_Id = '" +rs.getInt(1)+ "'");
				
				Circle circle = new Circle();
				circle.setCircleId(rs2.getInt(1));
				circle.setCircleName(rs2.getString(2));
				circle.setCircleOwner(rs2.getInt(3));
				circle.setCircleType(rs2.getString(4));
				
				list.add(circle);
			}
			return list;
		}catch(Exception ex){return null;}
	}
	
	/*
	 * Returns a list of user ID's who are in a specific circle
	 */
	public ArrayList<Integer> getUserIdList(int circleId){
		try{
			ResultSet rs = stmt.executeQuery("SELECT CustomerID FROM circlemembership WHERE CircleID = '" +circleId+ "'");
			ArrayList<Integer> list = new ArrayList<Integer>();
			
			while(rs.next())
				list.add(rs.getInt(1));
			
			return list;
		}catch(Exception ex){return null;}
	}
	
	
	//gets all circles from a specific user, and all members for each circle
	public ArrayList<ArrayList<User>> getAllUsers(int userId){
		try{
			ArrayList<ArrayList<User>> mainList = new ArrayList<ArrayList<User>>();
			
			ResultSet rs1, rs2, rs3, rs4;
			rs1 = stmt.executeQuery("SELECT Circle_Id FROM circles WHERE Owner_Of_Circle = '" +userId+ "'");
			
			//get each individual Circle ID for a user
			while(rs1.next()){
				int circleId = rs1.getInt(1);
				rs2 = stmt.executeQuery("SELECT CustomerID FROM circlemembership WHERE CircleID = '" +circleId+ "'");
				
				ArrayList<User> tempList = new ArrayList<User>();
				
				//for each circle, get all users in that circle
				while(rs2.next()){
					int user = rs2.getInt(1);
					
					rs3 = stmt.executeQuery("SELECT * FROM users WHERE User_Id = '" +user+ "'");
					User temp = new User(rs3.getInt(1));
					temp.setUserId(rs3.getInt(2));
					temp.setCreationDate(rs3.getString(3));
					temp.setRating(rs3.getInt(4));
						
					rs4 = stmt.executeQuery("SELECT * FROM persons WHERE SSN = '" +temp.getSSN()+ "'");
					temp.setLastName(rs4.getString(2));
					temp.setFirstName(rs4.getString(3));
					temp.setAddress(rs4.getString(4));
					temp.setCity(rs4.getString(5));
					temp.setState(rs4.getString(6));
					temp.setZip(rs4.getInt(7));
					temp.setPhone(rs4.getInt(8));
					temp.setEmail(rs4.getString(9));
					temp.setPassword(rs4.getString(10));
					
					tempList.add(temp);
				}
				mainList.add(tempList);
			}
			return mainList;
		}catch(Exception ex){return null;}
	}
}
